package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Model.Shop;
import utils.DBHelper;

public class ShopDao {
	Connection conn = null;
	PreparedStatement ps;
	ResultSet rs;
	String sql = "";

	public ShopDao() {
		conn = DBHelper.getConnection();
	}
	public boolean insert(Shop shop) {
		sql = "insert into tb_shop(shopname,address,phonenum,intro,pic,comment,level) values(?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, shop.getShopname());
			ps.setString(2, shop.getAddress());
			ps.setString(3, shop.getPhonenum());
			ps.setString(4, shop.getIntro());
			ps.setString(5, shop.getPic());
			ps.setString(6, shop.getComment());
			ps.setInt(7, shop.getLevel());
			int k = ps.executeUpdate();
			if (k > 0)
				return true;
			else
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;

	}
    public ArrayList<Shop> getAll(){
    	ArrayList<Shop> list = new ArrayList<Shop>();
    	sql = "select * from tb_shop where shop_id !=1";
    	try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
	    	
	    	while(rs.next()) {
	    		int shop_id = rs.getInt("shop_id");
	    		String shopname = rs.getString("shopname");
	    		String address = rs.getString("address");
	    		String intro = rs.getString("intro");
	    		int level = rs.getInt("level");
	    		String pic = rs.getString("pic");
	    		String phonenum = rs.getString("phonenum");
	    		String comment = rs.getString("comment");
	    		Shop shop = new Shop(shop_id,shopname,address,phonenum,intro,pic,comment,level);
	    		list.add(shop);
	    	}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;
    }
    public Shop getShopById(int id) {
    	Shop shop = null;
    	sql = "select * from tb_shop where shop_id = ?";
    	try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				
	    		String shopname = rs.getString("shopname");
	    		String address = rs.getString("address");
	    		String intro = rs.getString("intro");
	    		int level = rs.getInt("level");
	    		String pic = rs.getString("pic");
	    		String phonenum = rs.getString("phonenum");
	    		String comment = rs.getString("comment");
	    	    shop = new Shop(id,shopname,address,phonenum,intro,pic,comment,level);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return shop;
    }
    public boolean update(Shop shop) {
    	sql = "update tb_shop set shopname=?,address=?,phonenum=?,intro=?,pic=?,comment=?,level=? where shop_id=?";
    	try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, shop.getShopname());
			ps.setString(2, shop.getAddress());
			ps.setString(3, shop.getPhonenum());
			ps.setString(4, shop.getIntro());
			ps.setString(5, shop.getPic());
			ps.setString(6, shop.getComment());
			ps.setInt(7, shop.getLevel());
			System.out.println(shop.getShop_id());
			ps.setInt(8, shop.getShop_id());
			int k = ps.executeUpdate();
			if(k>0)return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return false;
    }
    public boolean delete(int id) {
    	sql = "delete from tb_shop where shop_id=?";
    	try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int k = ps.executeUpdate();
			if(k>0)return true;
			else return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return false;
    }
    public ArrayList<Shop> getShopsByCondition(String condition,String value){
    	sql = "select * from tb_shop where "+condition+" like '%"+value+"%'";
    	ArrayList<Shop> list = new ArrayList<Shop>();
    	try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
	    		int shop_id = rs.getInt("shop_id");
	    		String shopname = rs.getString("shopname");
	    		String address = rs.getString("address");
	    		String intro = rs.getString("intro");
	    		int level = rs.getInt("level");
	    		String pic = rs.getString("pic");
	    		String phonenum = rs.getString("phonenum");
	    		String comment = rs.getString("comment");
	    		Shop shop = new Shop(shop_id,shopname,address,phonenum,intro,pic,comment,level);
	    		list.add(shop);
	    	}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return list;
    }
    public boolean findShopByName(String name) {
    	sql = "select * from tb_shop where shopname = ?";
    	try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			if(rs.next())return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return false;
    }
    public Shop getShopByFoodId(int id) {
    	sql = "select t2.* from tb_food t1,tb_shop t2 where t1.shop_id = t2.shop_id and t1.food_id = ?";
    	try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
if(rs.next()) {
				int shop_id = rs.getInt("shop_id");
	    		String shopname = rs.getString("shopname");
	    		String address = rs.getString("address");
	    		String intro = rs.getString("intro");
	    		int level = rs.getInt("level");
	    		String pic = rs.getString("pic");
	    		String phonenum = rs.getString("phonenum");
	    		String comment = rs.getString("comment");
	    	    Shop shop = new Shop(shop_id,shopname,address,phonenum,intro,pic,comment,level);
	    	    return shop;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return null;
    }
}
